package com.util.sql;

import com.util.servlet.BaseController;
import org.omg.CORBA.OBJ_ADAPTER;

import javax.servlet.ServletContext;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ExecutionException;

/**
 * 数据库操作
 * 连接SQL参考： https://www.cnblogs.com/chenlove/p/9159227.html
 * 如果连接失败telnet 127.0.0.1 1433   连不通需要修改配置。
 * 封装参考：https://blog.csdn.net/huxu981598436/article/details/47084079
 */
public class SqlServerHelper  {
    private static  String url;
    private static String userName;
    private static String password;
    private static String driver;

    /**
     * 连接对象
     */
   private  static Connection conn = null;
    /**
     * 数据库操作对象
     */
    private static PreparedStatement stmt = null;
    /**
     * 储存过程
     */
    private static CallableStatement callableStatement = null;
    /**
     * 结果集对象
     */
   private static ResultSet result = null;


    /**
     * 静态块加载驱动
     */
    static {
        getConfigValue();
        try{
            Class.forName(driver);
        }catch (ClassNotFoundException e){
            System.out.println(String.format("加载驱动错误:%s",e.getMessage()));
        }
    }

    /**
     * 获取配置文件参数
     */
    public  static void getConfigValue(){
        InputStream file = null;
        //读取流读取文件
            try {
                //InputStream file = new FileInputStream("src/main/resources/application.yml");//绝对路径
                file = Object.class.getResourceAsStream("/application.yml");//相对路径
                if (file == null) {
                    System.out.println(String.format("未读取到Resources配置，载入war包配置"));
                    try {


                        //file = Object.class.getResourceAsStream("WEB-INF/classes/applcation.yml");
                      file = new FileInputStream("bbs-1.0.war/WEB-INF/application.yml");//绝对路径
                    } catch (Exception e) {
                        System.out.println(String.format("未读取到war包配置，手动载入配置")+e.getMessage());
                        url = "jdbc:sqlserver://localhost:1433;DatabaseName=bbs";
                        driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
                        userName = "sa";
                        password = "123456";
                        return;
                    }
                }
                System.out.println(file);
                SqlConfigService configService = SqlConfigFactory.readProperties(file);
                if (configService == null) {
                    return;
                }
                url = configService.getUrl();
                userName = configService.getUserName();
                password = configService.getPassword();
                driver = configService.getDriver();
                System.out.println(String.format("读取配置文件【userName:%s】，【password:%s】,【url:%s】,【driver:%s】"
                        , userName, password, url, driver));
            }catch (Exception e){
                System.out.println("获取数据库配置错误,请检验配置设置"+e.getMessage());
            }
            }





    /**
     * 数据库连接
     */
    public static Connection getConnection(){
        try {
            conn = DriverManager.getConnection(url,userName,password);
        } catch (SQLException e) {

            System.out.println(String.format("连接错误:%s",e.getMessage()));
        }
        return conn;
    }

    /**
     * 关闭连接
     */
    private static void close(){
        try {
            //关闭结果集
            if(result != null){
                result.close();
            }
            //关闭操作对象
            if(stmt != null){
                stmt.close();
            }
            //关闭回滚对象
            if(callableStatement != null){
                callableStatement.close();
            }
            //关闭连接对象
            if(conn != null){
                conn.close();
            }

        } catch (Exception e) {
            System.out.println(String.format("闭关失败:%s",e.getMessage()));
        }

    }

    /**
     * 增 删 改 统一 方法
     * @param sql
     * @param agrs
     * @return 受影响行数
     */
    public static int execUpdate(String sql,Object...agrs){
        System.out.println(String.format("执行的SQL：%s,参数：%s",sql,agrs));
        //受影响的行数
        int influent = 0;

        try {
            //获取连接
            conn = getConnection();
            //调用sql
            stmt = conn.prepareStatement(String.format(sql,agrs));
            //执行
            influent = stmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(String.format("执行失败:",e.getMessage()));
            e.printStackTrace();
        }finally {
            close();
        }

        return influent;
    }

    /**
     * 获取查询结果的结果集
     * @param sql
     * @param agrs
     * @return
     */
    private static ResultSet queryRS(String sql,Object ...agrs){

        try {
            conn  = getConnection();
            stmt = conn.prepareStatement(String.format(sql,agrs));
            result = stmt.executeQuery();
        } catch (SQLException e) {
            System.out.println(String.format("获取RESULT结果集失败:%s",e.getMessage()));
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 获取一行一列的结果集
     * @param sql
     * @param agrs
     * @return
     */
    public static Object  querySingle(String sql,Object...agrs){
        System.out.println("执行sql:"+sql);
        Object object = null;
        try {
            conn = getConnection();
            stmt = conn.prepareStatement(String.format(sql,agrs));
            result = stmt.executeQuery();
            if(result.next()){
                object = result.getObject(1);
            }
        } catch (Exception e) {
            System.out.println(String.format("获取RESULT集失败:%s",e.getMessage()));
        } finally {
            close();
        }
        return object;
    }

    /**
     * 获取List结果集
     * @param sql
     * @param agrs
     * @return
     */
    public static List<Object> queryList(String sql,Object...agrs){
        System.out.println(String.format("执行sql:%s",sql));
        //执行SQL,获取结果集
        ResultSet rs = queryRS(sql,agrs);
        //创建ResultSetMetaData对象
        ResultSetMetaData rsmd = null;
        //结果集列数
        int resultCount = 0;
        try {
            rsmd = rs.getMetaData();
            //获取结果集列数
            resultCount = rsmd.getColumnCount();
        } catch (SQLException e) {
            System.out.println(String.format("获取列数失败：%s",e.getMessage()));
        }
        //创建List
        List<Object> list = new ArrayList<>();
        try {
            //将结果集保存到List中
            while(rs.next()){
                Map<String,Object> map = new HashMap<>();
                for(int i = 1;i<resultCount;i++){
                    //将结果添加到MAP中
                    map.put(rsmd.getColumnLabel(i),rs.getObject(i));
                }
                //将结果添加到List集合中
                list.add(map);
            }
        } catch (Exception e) {
            System.out.println(String.format("获取LIST失败",e.getMessage()));
        } finally {
            close();
        }
        return list;
    }

    /**
     * 执行储存过程
     * @param sql
     * @param outParam
     * @param sqlType
     * @param args
     * @return
     */
    public Object queryProc(String sql,int outParam,int sqlType,Object...args){
        Object object = null;
        conn = this.getConnection();

        try {
            //调用储存过程
            callableStatement = conn.prepareCall(String.format(sql,args));
            //注册输出参数
            callableStatement.registerOutParameter(outParam,sqlType);
            //执行
            callableStatement.execute();
            //得到输出参数
            object = callableStatement.getObject(outParam);
        } catch (SQLException e) {
            System.out.println(String.format("执行储存过程失败:%s",e.getMessage()));
        } finally {
            this.close();
        }
        return object;
    }

    public static void main(String[] args) {
        SqlServerHelper helper = new SqlServerHelper();
        String sql = "INSERT INTO QQ (name,pwd) values('%s',%s)";
       //helper.execUpdate(sql,"王后",18);
        String reSql = "SELECT * FROM QQ WHERE  1 = 1 ";
      // System.out.println(helper.queryRS(sql,4));
       //List list = (List) helper.queryList(reSql,null);
        //System.out.println(list);
        //System.out.pris
        //
        // ntln(helper.queryProc(reSql,0,0,null));

        String s = "SELECT COUNT(1) FROM [Artical] WHERE 1 = 1";
        System.out.println(SqlServerHelper.querySingle(s));
    }
}
